#########################################################################
# Define your tables below, for example                                 #
#                                                                       #
# >>> db.define_table('mytable',SQLField('myfield','string'))           #
#                                                                       #
# Fields can be 'string','text','password','integer','double','booelan' #
#       'date','time','datetime','blob','upload', 'reference TABLENAME' #
# There is an implicit 'id integer autoincrement' field                 #
# Consult manual for more options, validators, etc.                     #
#                                                                       #
# More API examples for controllers:                                    #
#                                                                       #
# >>> db.mytable.insert(myfield='value')                                #
# >>> rows=db(db.mytbale.myfield=='value).select(db.mytable.ALL)        #
# >>> for row in rows: print row.id, row.myfield                        #
#########################################################################


# Custom Classes and Modules
auth = local_import('auth', reload=False, app=request.application)
fhelper = local_import('forumhelper', reload=False, app=request.application)
#from applications.pyforum.modules.auth import CustomAuthentication
#from applications.pyforum.modules.forumhelper import ForumHelper

db=SQLDB("mysql://username:passwd@localhost:3306/pyforum", pool_size=10)

# zf_forum_category
# Categories just provide a "container" for each discussion board (zf_forum).
# cat_desc is optional and it may be used to provide a longer description of the category
# cat_public is a flag that determines if an anonymous user can view the forum
# cat_visible_to controls the roles for which the category be available for viewing
db.define_table('zf_forum_category',
                db.Field('cat_name', 'string', length=255, required=True),
                db.Field('cat_desc', 'text', default=''),
                db.Field('cat_visible_to', 'string', length=255, required=True),
                db.Field('cat_sort', 'integer', required=True, default=0),
                migrate=False)

# zf_forum
# This is the topics container and in itself it a containee of a category
db.define_table('zf_forum',
                db.Field('cat_id', db.zf_forum_category),
                db.Field('forum_title', 'string', length=255, required=True),
                db.Field('forum_desc', 'text', default=''),
                db.Field('moderation_flag', 'boolean', required=True, default=False),
                db.Field('anonymous_viewaccess', 'boolean', required=True, default=True),
                db.Field('add_postings_access_roles', 'string', length=255, default=''),
                db.Field('reply_postings_access_roles', 'string', length=255, default=''),
                db.Field('include_latest_topics', 'boolean', required=True, default=True),
                db.Field('forum_sort', 'integer', required=True, default=0),
                migrate=False
                ) 

# zf_topic
# This is the actual topic, it depends exclusively on zf_forum
db.define_table('zf_topic',
                db.Field('forum_id', db.zf_forum),
                db.Field('title', 'string', length=255, required=True),
                db.Field('content', 'text', required=True),
                db.Field('parent_id', 'integer', required=True, default=0),
                db.Field('creation_user', 'string', length=255, required=True),
                db.Field('creation_date', 'datetime', required=True),
                db.Field('modifying_user', 'string', length=255, required=True),
                db.Field('modifying_date', 'datetime', required=True),
                db.Field('hits', 'integer', required=True, default=0),
                db.Field('parent_flag', 'boolean', required=False),
                db.Field('locked_flag', 'boolean', required=False),
                db.Field('disabled_flag', 'boolean', required=False),
                db.Field('sticky_flag', 'boolean', required=False),
                db.Field('poll_flag', 'boolean', required=False),
                db.Field('system_announcement_flag', 'boolean', required=False),
                db.Field('reply_to_topic_id', 'integer', required=True, default=0),
                db.Field('ip_address', 'string', required=True),
                migrate=False
                )

# System Support Tables

# System Properties - Used by the system itself
db.define_table('zf_system_properties',
                db.Field('property_name', 'string', length=128, required=True),
                db.Field('property_desc', 'text', required=True),
                db.Field('property_value', 'text', required=True),
                migrate=False
                )

# Member Properties (Skeleton) (Available member Properties)
db.define_table('zf_member_properties_skel',
                db.Field('property_name', 'string', length=128, required=True),
                db.Field('property_desc', 'text', required=True),
                db.Field('member_editable', 'boolean', default=False),
                migrate=False
                )

# Member Properties (For each member)
db.define_table('zf_member_properties',
                db.Field('auth_user', 'string', length=255, required=True),
                db.Field('property_id', db.zf_member_properties_skel),
                db.Field('property_value', 'string', length=255, required=True),
                migrate=False
                )

# Avatars
db.define_table('zf_member_avatars',
                db.Field('content_type', 'string', length=128, required=True),
                db.Field('auth_user', 'string', length=255, required=True),
                db.Field('avatar_image', 'blob', required=True, default=None),
                db.Field('avatar_active', 'boolean', required=True, default=True),
                migrate=False
                )

# Member Subscriptions (subscription_type =  ENUM('f', 't') --> Forum or Topic
db.define_table('zf_member_subscriptions',
                db.Field('auth_user', 'string', length=255, required=True),
                db.Field('subscription_id', 'integer', required=True, default=0),
                db.Field('subscription_type', 'string', required=True),
                db.Field('subscription_active', 'boolean', default=True, required=True),
                migrate=False
                )

db.define_table('zf_member_subscriptions_notification',
                db.Field('auth_user', 'string', length=255, required=True, default=''),
                db.Field('subscription_id', 'integer', required=True, default=0),
                db.Field('subscription_type', 'string', required=True),
                db.Field('creation_date', 'datetime', required=True),
                db.Field('is_processed', 'boolean', default=False, required=True),
                migrate=False
                )

# Member Personal Messages
db.define_table('zf_pm_categories',
                db.Field('display_order', 'integer', required=True, default=0),
                db.Field('name', 'string', length=128, required=True),
                migrate=False
                )

db.define_table('zf_pm',
                db.Field('cat_id', db.zf_pm_categories),
                db.Field('read_flag', 'boolean', default=False, required=True),
                db.Field('auth_user', 'string', length=255, required=True),
                db.Field('from_auth_user', 'string', length=255, required=True),
                db.Field('subject', 'string', length=255, required=True),
                db.Field('message', 'text', required=True),
                db.Field('creation_date', 'datetime', required=True),
                migrate=False
                )

db.define_table('zf_admin_messages',
                db.Field('auth_user', 'string', length=255, required=True),
                db.Field('subject', 'string', length=255, required=True),
                db.Field('message', 'text', required=True),
                db.Field('creation_date', 'datetime', required=True),
                db.Field('read_flag', 'boolean', default=False, required=True),
                migrate=False
                )

db.define_table('zf_available_languages',
                db.Field('language_code', 'string', required=True, length=5),
                db.Field('language_desc', 'string', required=True, length=255),
                db.Field('enabled', 'boolean', required=True, default=True),
                migrate=False
                )

db.define_table('zf_member_rank',
                db.Field('rank_name', 'string', required=True, length=128),
                db.Field('rank_value_min', 'integer', required=True, default=0),
                migrate=False
                )
                
db.define_table('zf_topic_inappropriate',
                db.Field('topic_id', db.zf_topic),
                db.Field('child_id', 'integer', required=True),
                db.Field('creation_user', 'string', required=True, length=255),
                db.Field('creation_date', 'datetime', required=True),
                db.Field('read_flag', 'boolean', default=False, required=True),
                migrate=False
                )

db.define_table('zf_member_banned',
                db.Field('auth_user', 'string', required=True, length=255),
                db.Field('ban_date', 'datetime', required=True),
                migrate=False
                )

db.define_table('zf_ip_banned',
                db.Field('ip', 'string', length=255, required=True),
                db.Field('ban_date', 'datetime', required=True),
                migrate=False
                )

## Authentication Schema (3 tables)
db.define_table('auth_users',
                db.Field('auth_alias', 'string', length=128, required=True),
                db.Field('auth_email', 'string', length=128, required=True),
                db.Field('auth_passwd', 'string', length=128, required=True),
                db.Field('auth_created_on', 'string', required=True),
                db.Field('auth_modified_on', 'string', required=True),
                db.Field('is_enabled', 'boolean', required=True, default=True),
                migrate=False
               )

# Roles: Manager, Approver, Reviewer, etc
db.define_table('auth_roles',
                db.Field('auth_role_name', 'string', length=128, required=True),
                migrate=False
               )

# User/Role Mapping
db.define_table('auth_user_role',
                db.Field('auth_user_id', db.auth_users),
                db.Field('auth_role_id', db.auth_roles),
                migrate=False
               )

 # Instantiate Authentication
auth_user = auth.CustomAuthentication(request, response, session, cache, T, db)
# Make Systemwide methods available
forumhelper = fhelper.ForumHelper(request, response, session, cache, T, db, auth_user)

